<?php
  class Model {
    public $_db;
    public $table_name;
    /*
      array(
        $key_in_db => array(
          as   =>   $key_in_api,
          type =>   'number' || 'string' || 'json',
          max_length => 11,
          min_length => 5,
          unique => true || false,
          required => true || false,
          format => email || phone || regex
        )
      )
      schemas should not include main_key
    */
    public $schemas = array();
    /*
      array(
        'group' => array(
          'this_key' => 'Id',
          'other_model_name' => 'VlinkGroupModel',
          'other_key' => 'Id',
          'assoc_table_name' => 't_vlinkusergroupassociation',
          'assoc_this_key' => 'UserId',
          'assoc_other_key' => 'GroupId'
        )
      )
    */
    public $associations = array();
    public $main_key = 'id';
    public $main_key_as = 'id';

    public function __construct($db) {
      $this->_db = $db;
    }
    
    public function dispense() {
      $b = new Bean($this);
      $b->setId(0);
      return $b;
    }

    public function save($bean) {
      $response = array();
      try {
        $this->onUpdate($bean);
        $this->validate($bean);
        if ($bean->getId() == 0) {
          // create
          $this->query($this->buildCreateSQL($bean));
          $res = $this->query("SELECT LAST_INSERT_ID()");
          if ($res[0]) {
            $bean->setId($res[0]['LAST_INSERT_ID()']);
          }
          $this->onCreate($bean);
        } else {
          // update
          $this->query($this->buildUpdateSQL($bean));
        }
      } catch (Exception $e) {
        $response['msg'] = 'err';
        $response['error'] = $e->getMessage();
        return $response;
      }

      $response['msg'] = 'ok';
      $response[$this->main_key_as] = $bean->getId();
      return $response;
    }

    public function trash($bean) {
      $this->onTrash($bean);
      if ($bean->getId() == 0) {
        return $this;
      }
      $this->query($this->buildRemoveSQL($bean));
      $bean->clear();
      return $this;
    }

    public function load($id) {
      $args[$this->main_key] = $id;
      $bean = $this->findOne($args);
      return $bean;
    }

    public function find($args, $limit = null, $order = null) {
      $res = $this->query($this->buildFindSQL($args, $limit, $order));
      return $this->buildBeansFromRows($res);
    }

    public function findOne($args, $order = null) {
      $beans = $this->find($args, 1, $order);
      if (count($beans) > 0) {
        return $beans[0];
      } else {
        return null;
      }
    }

    public function findWithSQL($sql) {
      $res = $this->query('SELECT * FROM ' . $this->table_name . ' WHERE ' . $sql);
      return $this->buildBeansFromRows($res);
    }

    public function getAssociations($bean, $name) {
      if (!isset($this->associations[$name])) {
        return array();
      }
      $association = $this->associations[$name];
      
      $ReflectionClass = new ReflectionClass($association['other_model_name']);
      $Model = $ReflectionClass->newInstance($this->_db);
      
      $omtn = $Model->table_name;
      $atn = $association['assoc_table_name'];
      $ok = $association['other_key'];
      $aok = $association['assoc_other_key'];
      $tv = $bean->get($association['this_key']);
      $atk = $association['assoc_this_key'];

      $other_rows = $this->query($this->buildFindAssocSQL($omtn, $atn, $ok, $aok, $tv, $atk));
      return $Model->buildBeansFromRows($other_rows);
    }

    public function associateWith($bean, $name, $other_value) {
      if (!isset($this->associations[$name])) {
        return $this;
      }
      $association = $this->associations[$name];
      
      $ReflectionClass = new ReflectionClass($association['other_model_name']);
      $Model = $ReflectionClass->newInstance($this->_db);
      
      $atn = $association['assoc_table_name'];
      $aok = $association['assoc_other_key'];
      $tv = $bean->get($association['this_key']);
      $atk = $association['assoc_this_key'];
      $this->query($this->buildCreateAssocSQL($atn, $atk, $aok, $tv, $other_value));
      return $this;
    }

    public function disassociateWith($bean, $name, $other_value) {
      if (!isset($this->associations[$name])) {
        return $this;
      }
      $association = $this->associations[$name];
      
      $ReflectionClass = new ReflectionClass($association['other_model_name']);
      $Model = $ReflectionClass->newInstance($this->_db);
      
      $atn = $association['assoc_table_name'];
      $aok = $association['assoc_other_key'];
      $tv = $bean->get($association['this_key']);
      $atk = $association['assoc_this_key'];
      $this->query($this->buildCreateAssocSQL($atn, $atk, $aok, $tv, $other_value));
      return $this;
    }
    
    private function buildBeansFromRows($rows) {
      $beans = array();
      for ($i = 0; $i < count($rows); $i++) { 
        $b = new Bean($this);
        $b->import($rows[$i], true);
        array_push($beans, $b);
      }
      return $beans;
    }

    // function called after INSERT
    // override this func in child
    public function onCreate($bean) {}
    
    // function called on INSERT and UPDATE
    // override this func in child
    public function onUpdate($bean) {}

    // function called on DELETE
    // override this func in child
    public function onTrash($bean) {}
    
    // called on bean->export
    public function onExport($row) {
      return $row;
    }

    private function validate($bean) {
      foreach ($this->schemas as $key => $schema) {
        if (isset($schema['required']) and $bean->get($key) == null) {
          throw new Exception($key . ' required but not found');
          continue;
        }
        if (isset($schema['default']) and $bean->get($key) == null) {
          $bean->set($key, $schema['default']);
        }
        if ($bean->get($key) != null) {
          $this->validateValue($key, $bean->get($key), $schema);
        }
        if ($bean->get($key) != null && isset($schema['unique'])) {
          $args = array();
          $args[$key] = $bean->get($key);
          $_bean = $this->findOne($args);
          if ($_bean == null) {
            continue;
          }
          if ($bean->getId() != 0) {
            if ($bean->getId() == $_bean->getId()) {
              continue; 
            }
            throw new Exception($key . ': ' . $bean->get($key) . ' already exists');
            return;
          }
          throw new Exception($key . ': ' . $bean->get($key) . ' already exists');
          return;
        }
      }
    }

    private function validateValue($key, $value, $schema) {
      if (isset($schema['max_length'])) {
        if (strlen($value) > $schema['max_length']) {
          throw new Exception($key . ' too long');
          return;
        }
      }
      if (isset($schema['min_length'])) {
        if (strlen($value) < $schema['min_length']) {
          throw new Exception($key . ' too short');
          return;
        }
      }
      if (isset($schema['format'])) {
        if ($schema['format'] == 'email') {
          if (!filter_var($value, FILTER_VALIDATE_EMAIL)) {
            throw new Exception('invalid email address');
            return;
          }
        } else if ($schema['format'] == 'phone number') {
          if (is_string($value)) {
            $value = (int)$value;
          }
          if (preg_match("/^13[0-9]{1}[0-9]{8}$|15[012356789]{1}[0-9]{8}$|18[012356789]{1}[0-9]{8}$|14[57]{1}[0-9]$/", $value) != 1) {
            throw new Exception('invalid phone number');
            return;
          }
        } else {
          if (preg_match($schema['format'], $value) != 1) {
            throw new Exception('invalid format');
            return;
          }
        }
      }
    }
    
    private function query($sql) {
      $response = array();
      $result = $this->_db->query($sql);
      if (!$result) {
        throw new Exception($this->_db->error);
        return;
      }
      if (!is_object($result) || $result->num_rows < 1) {
        return $response;
      }
      while ($row = $result->fetch_assoc()) {
        array_push($response, $row);
      }
      $result->close();
      return $response;
    }

    private function prepareValueForSQL($value, $type = 'string') {
      if ($type == 'json' && gettype($value) == 'array') {
        $_value = json_encode($value);
      } else {
        $_value = $value;
      }
      return "'" . mysql_escape_string($_value) . "'";
    }

    private function buildCreateSQL($bean) {
      // INSERT `t_user` (name, gender) values(aki, male)
      $sql = 'INSERT `' . $this->table_name . '` ';
      $keys = '(';
      $values = 'values(';
      foreach ($this->schemas as $key => $schema) {
        if ($bean->get($key) == null) {
          continue;
        }
        $keys .= $key . ', ';
        $values .= $this->prepareValueForSQL($bean->get($key), $schema['type']) . ", ";
      }
      if (strlen($keys) != 1) {
        $keys = substr($keys, 0, -2);
      }
      $keys .= ')';
      if (strlen($values) != 7) {
        $values = substr($values, 0, -2);
      }
      $values .= ')';
      $sql = $sql . $keys . ' ' . $values;
      return $sql;
    }

    private function buildRemoveSQL($bean) {
      // DELETE FROM `t_user` WHERE id = user_id
      return 'DELETE FROM `' . $this->table_name . '` WHERE ' . $this->main_key . ' = ' . $bean->getId();
    }

    private function buildUpdateSQL($bean) {
      // UPDATE `t_user` SET aaa = bbb, ccc = ddd WHERE id = user_id
      $sql = 'UPDATE `' . $this->table_name . '` SET ';
      $conds = '';
      foreach ($this->schemas as $key => $schema) {
        if ($bean->get($key) == null) {
          continue;
        }
        $conds .= $key . ' = ' . $this->prepareValueForSQL($bean->get($key), $schema['type']) . ', ';
      }
      if (strlen($conds) != 0) {
        $conds = substr($conds, 0, -2);
      }
      $sql .= $conds . ' WHERE ' . $this->main_key . " = '" . $bean->getId() . "'";
      return $sql;
    }

    private function buildFindSQL($args = array(), $limit = null, $order = null) {
      // SELECT * FROM `t_user` WHERE aaa = bbb ORDER BY aaa DECENT LIMIT 20
      $sql = 'SELECT * FROM `' . $this->table_name . '` WHERE ';
      $conds = '';
      foreach ($args as $key => $value) {
        if ($key == $this->main_key) {
          $conds .= $this->main_key . ' = ' . $this->prepareValueForSQL($value) . ' AND ';
          break;
        }
        foreach ($this->schemas as $_key => $schema) {
          if (($key == $_key) || (isset($schema['as']) && $schema['as'] == $key)) {
            $conds .= $_key . ' = ' . $this->prepareValueForSQL($value, $schema['type']) . ' AND ';
            break;
          }
        }
      }

      if (strlen($conds) != 0) {
        $conds = substr($conds, 0, -4);
      }
      $sql .= $conds;
      
      if ($limit != null) {
        $sql .= ' LIMIT ' . $limit;
      }
      if ($order != null) {
        $sql .= ' ORDER BY ' . $order;
      }
      
      return $sql;
    }

    private function buildFindAssocSQL($other_model_table_name, $assoc_table_name, $other_key, $assoc_other_key, $this_value, $assoc_this_key) {
      return 'SELECT * FROM `' . $other_model_table_name . '` om, `' . $assoc_table_name . '` a WHERE om.' . $other_key . ' = a.' . $assoc_other_key . ' AND a.' . $assoc_this_key . ' = ' . $this->prepareValueForSQL($this_value);
    }

    private function buildCreateAssocSQL($assoc_table_name, $assoc_this_key, $assoc_other_key, $this_value, $other_value) {
      return 'INSERT `' . $assoc_table_name . '`(' . $assoc_this_key . ',' . $assoc_other_key . ') values(' . $this->prepareValueForSQL($this_value) . ', ' . $this->prepareValueForSQL($other_value) . ')';
    }

    private function buildRemoveAssocSQL($assoc_table_name, $assoc_this_key, $assoc_other_key, $this_value, $other_value) {
      return 'DELETE FROM `' . $assoc_table_name . '` WHERE ' . $assoc_this_key . '=' . $this->prepareValueForSQL($this_value) . ' AND ' . $assoc_other_key . ' = ' . $this->prepareValueForSQL($other_value);
    }
  }
?>
